/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/* 
 * File:   CSwitchBase.cpp
 * Author: Administrator
 * 
 * Created on 2017年11月22日, 下午6:01
 */

#include "CSwitchifTable.h"
#include "PublicLog.h"
#include "SnmpApi.h"
#include "SnmpClient.h"
#include "SnmpResultApi.h"
#include "PublicSnmpCommon.h"
#include "PublicMySql.h"
#include "Common.h"
#include "PublicRRDTool.h"
CSwitchifTable::CSwitchifTable(std::string ip){
	m_ip=ip;
	m_initok=1;
	m_createsql=MakeCreateIfFlowTableSQL(m_ip.c_str());
	snprintf(m_rrddir,sizeof(m_rrddir),"%s/%s",g_sysconf.rrddatapath,m_ip.c_str());
	if(is_dir_exist(m_rrddir)<0){
		if(CreateFolder(m_rrddir,1)<0){
			WRONG("创建目录失败:%s",m_rrddir);
			m_initok=0;
		}
	}
	snprintf(m_flowtable,sizeof(m_flowtable),"ifflow_%s",m_ip.c_str());
}

CSwitchifTable::~CSwitchifTable(){
	if(m_createsql){
		free(m_createsql);
		m_createsql=NULL;
	}
}
//相关OID
//static const SnmpOidInfo OID_ifTable={"ifTable",{1,3,6,1,2,1,2,2},8};
static const SnmpOidInfo OID_ifEntry={"ifEntry",{1,3,6,1,2,1,2,2,1},9};
static const SnmpOidInfo OID_ifDescr={"ifDescr",{1,3,6,1,2,1,2,2,1,2},10,ASN_OCTET_STR};
static const SnmpOidInfo OID_ifType={"ifType",{1,3,6,1,2,1,2,2,1,3},10,ASN_INTEGER};
static const SnmpOidInfo OID_ifMtu={"ifMtu",{1,3,6,1,2,1,2,2,1,4},10,ASN_INTEGER};

static const SnmpOidInfo OID_ifPhysAddress={"ifPhysAddress",{1,3,6,1,2,1,2,2,1,6},10,ASN_OCTET_STR};
static const SnmpOidInfo OID_ifAdminStatus={"ifAdminStatus",{1,3,6,1,2,1,2,2,1,7},10,ASN_INTEGER};
static const SnmpOidInfo OID_ifOperStatus={"ifOperStatus",{1,3,6,1,2,1,2,2,1,8},10,ASN_INTEGER};
static const SnmpOidInfo OID_ifLastChange={"ifLastChange",{1,3,6,1,2,1,2,2,1,9},10,ASN_TIMETICKS};

static const SnmpOidInfo OID_ifXEntry={"ifXEntry",{1,3,6,1,2,1,31,1,1,1},10};
static const SnmpOidInfo OID_ifName={"ifName",{1,3,6,1,2,1,31,1,1,1,1},11,ASN_OCTET_STR};
static const SnmpOidInfo OID_ifHCInOctets={"ifHCInOctets",{1,3,6,1,2,1,31,1,1,1,6},11,ASN_COUNTER64};
static const SnmpOidInfo OID_ifHCOutOctets={"ifHCOutOctets",{1,3,6,1,2,1,31,1,1,1,10},11,ASN_COUNTER64};
static const SnmpOidInfo OID_ifHighSpeed={"ifHighSpeed",{1,3,6,1,2,1,31,1,1,1,15},11,ASN_GAUGE};
static const SnmpOidInfo OID_ifConnectorPresent={"ifConnectorPresent",{1,3,6,1,2,1,31,1,1,1,17},11,ASN_INTEGER};
static const SnmpOidInfo OID_ifAlias={"ifAlias",{1,3,6,1,2,1,31,1,1,1,18},11,ASN_OCTET_STR};
static const SnmpOidInfo OID_ifCounterDiscontinuityTime={"ifCounterDiscontinuityTime",{1,3,6,1,2,1,31,1,1,1,19},11,ASN_TIMETICKS};
//处理数据
MAPIFTABLEINFO MakeifTable(netsnmp_variable_list*result){
	MAPIFTABLEINFO tmp;
	while(result){
		if(CheckTableOid(result,OID_ifEntry)!=0){
			result=result->next_variable;
			continue;
        }
		if(CheckOidType(result,OID_ifDescr)!=0){
			result=result->next_variable;
			continue;
        }
		int rootlen=OID_ifDescr.oidLen;
		uint32_t ifIndex=result->name[rootlen+0];	
		char ifDescr[256]={0};
		if(ReadStringFromResult(result,OID_ifDescr,ifDescr,sizeof(ifDescr)-1)==0){
			if(ifDescr[0]!='\0'){
				ifTableInfo info={0};
				strncpy(info.sys.ifFullDescr,ifDescr,sizeof(info.sys.ifFullDescr)-1);
				SimpleifDescr(ifDescr);
				strncpy(info.ifDescr,ifDescr,sizeof(info.ifDescr)-1);	
				info.ifCrc32=MakeCRC32(info.ifDescr,strlen(info.ifDescr));
				tmp.insert(std::pair<uint32_t,ifTableInfo>(ifIndex,info));
			}else{
				WRONG("ERR ifDescr:%s %u",ifDescr,ifIndex);
            }
        }
		result=result->next_variable;
    }
	return tmp;
}
void UpdateifTableVal(MAPIFTABLEINFO&ifTable,netsnmp_variable_list*result){
	time_t timestamp=time(NULL);
	while(result){
		int rootlen=0;
		if(CheckTableOid(result,OID_ifEntry,0)==0){
			rootlen=OID_ifEntry.oidLen+1;
		}else
		if(CheckTableOid(result,OID_ifXEntry)==0){
			rootlen=OID_ifXEntry.oidLen+1;
		}else{
			result=result->next_variable;
			continue;
        }
		uint32_t ifIndex=result->name[rootlen+0];
		MAPIFTABLEINFO::iterator it=ifTable.find(ifIndex);
		if(it==ifTable.end()){
			WRONG("ifTable未找到该索引:%u",ifIndex);
			result=result->next_variable;
			continue;
        }
		uint32_t Count32=0;
		uint64_t Count64=0;
		char StrData[256]={0};
		if(ReadCount32FromResult(result,OID_ifType,Count32)!=-1){
			it->second.sys.ifType=Count32;
			GetifTypeInfo(it->second.sys.ifType,it->second.sys.ifTypeDescr);
		}else
		if(ReadCount32FromResult(result,OID_ifMtu,Count32)!=-1){
			it->second.sys.ifMtu=Count32;
		}else
		if(ReadCount32FromResult(result,OID_ifAdminStatus,Count32)!=-1){
			it->second.sys.ifAdminStatus=Count32;
		}else
		if(ReadCount32FromResult(result,OID_ifOperStatus,Count32)!=-1){
			it->second.sys.ifOperStatus=Count32;
		}else
		if(ReadCount32FromResult(result,OID_ifLastChange,Count32)!=-1){
			it->second.sys.ifLastChange=Count32;
		}else
		if(ReadCount32FromResult(result,OID_ifHighSpeed,Count32)!=-1){
			it->second.sys.ifHighSpeed=Count32;
		}else
		if(ReadCount32FromResult(result,OID_ifConnectorPresent,Count32)!=-1){
			it->second.sys.ifConnectorPresent=Count32;
		}else
		if(ReadCount64FromResult(result,OID_ifHCInOctets,Count64)!=-1){
			it->second.octets.ifHCInOctets=Count64;
			it->second.timestamp=timestamp;
		}else
		if(ReadCount64FromResult(result,OID_ifHCOutOctets,Count64)!=-1){
			it->second.octets.ifHCOutOctets=Count64;
		}else
		if(ReadStringFromResult(result,OID_ifAlias,StrData,sizeof(StrData)-1)!=-1){
			if(StrData[0]!='\0'){
				strncpy(it->second.sys.ifAlias,StrData,sizeof(it->second.sys.ifAlias)-1);
			}
		}else
		if(ReadStringFromResult(result,OID_ifName,StrData,sizeof(StrData)-1)!=-1){
			if(StrData[0]!='\0'){
				strncpy(it->second.sys.ifName,StrData,sizeof(it->second.sys.ifName)-1);
			}
		}else{
			ReadMacFromResult(result,OID_ifPhysAddress,it->second.sys.ifPhysAddress);
        }
		result=result->next_variable;
    }
}
#if 0
void AddifInfoRequest(netsnmp_variable_list**varlist,SnmpOidInfo OidInfo,uint32_t ifIndex){
    OidInfo.oidName[OidInfo.oidLen++]=ifIndex;
    snmp_varlist_add_variable(varlist,OidInfo.oidName,OidInfo.oidLen,ASN_NULL,NULL,0);
}
#endif
void CSwitchifTable::UpDateifTable(){
	if(m_initok==0){
		return ;
	}
	INFO("Update ifTable Start:%s",m_ip.c_str());
	void*ss=OpenSnmp(m_ip.c_str());
	do{
		netsnmp_variable_list*result=netsnmp_sess_query_walkall(ss,OID_ifDescr);
		if(result!=NULL){
			m_ifTable.clear();
			m_ifTable=MakeifTable(result);
			SAFE_FREE_VARBIND(result);
		}else{
			CloseSnmp(ss);
			return;
        }
    }while(0);
	do{
		netsnmp_variable_list*result=NULL;
		MAPIFTABLEINFO::iterator it=m_ifTable.begin();
		while(it!=m_ifTable.end()){
			//rrd路径存储更新
			snprintf(it->second.rrdpath,sizeof(it->second.rrdpath),"%s/%u.rrd",m_rrddir,it->second.ifCrc32);
			AddSnmpRequestByNum(&result,OID_ifType,it->first);
			AddSnmpRequestByNum(&result,OID_ifMtu,it->first);
			AddSnmpRequestByNum(&result,OID_ifPhysAddress,it->first);
			AddSnmpRequestByNum(&result,OID_ifAdminStatus,it->first);
			AddSnmpRequestByNum(&result,OID_ifOperStatus,it->first);
			AddSnmpRequestByNum(&result,OID_ifLastChange,it->first);
			AddSnmpRequestByNum(&result,OID_ifName,it->first);
			AddSnmpRequestByNum(&result,OID_ifHCInOctets,it->first);
			AddSnmpRequestByNum(&result,OID_ifHCOutOctets,it->first);
			AddSnmpRequestByNum(&result,OID_ifHighSpeed,it->first);
			AddSnmpRequestByNum(&result,OID_ifConnectorPresent,it->first);
			AddSnmpRequestByNum(&result,OID_ifAlias,it->first);
			AddSnmpRequestByNum(&result,OID_ifCounterDiscontinuityTime,it->first);
			it++;
        }
		if(netsnmp_sess_query_get_ex(result,ss)==0){
			UpdateifTableVal(m_ifTable,result);
			SAFE_FREE_VARBIND(result);
		}else{
			WRONG("IP[%s]网卡采集失败",m_ip.c_str());
			SAFE_FREE_VARBIND(result);
			CloseSnmp(ss);
			return;
        }
    }while(0);
	CloseSnmp(ss);
	INFO("Update ifTable RRDData:%s",m_ip.c_str());
	UpLoadifTable();
	INFO("Update ifTable End:%s",m_ip.c_str());
}


void CSwitchifTable::UpLoadifTable(){
	if(m_ifTable.empty())return;
	const char*Head="INSERT INTO`ifTable`(`IP`,`ifIndex`,`ifCrc32`,"
	"`ifDescr`,`ifFullDescr`,`ifAlias`,`ifName`,"
	"`ifType`,`ifTypeDescr`,`ifMtu`,`ifSpeed`,"
	"`ifPhysAddress`,`ifAdminStatus`,`ifOperStatus`,"
	"`ifLastChange`,`ifConnectorPresent`,`ifCounterDiscontinuityTime`,`rrdpath`)VALUES";
	const char*End=	"ON DUPLICATE KEY UPDATE"
					"`ifIndex`=VALUES(`ifIndex`),"
					"`ifCrc32`=VALUES(`ifCrc32`),"
					"`ifDescr`=VALUES(`ifDescr`),"
					"`ifFullDescr`=VALUES(`ifFullDescr`),"
					"`ifAlias`=VALUES(`ifAlias`),"
					"`ifName`=VALUES(`ifName`),"
					"`ifType`=VALUES(`ifType`),"
					"`ifTypeDescr`=VALUES(`ifTypeDescr`),"
					"`ifMtu`=VALUES(`ifMtu`),"
					"`ifSpeed`=VALUES(`ifSpeed`),"
					"`ifPhysAddress`=VALUES(`ifPhysAddress`),"
					"`ifAdminStatus`=VALUES(`ifAdminStatus`),"
					"`ifOperStatus`=VALUES(`ifOperStatus`),"
					"`ifLastChange`=VALUES(`ifLastChange`),"
					"`ifConnectorPresent`=VALUES(`ifConnectorPresent`),"
					"`ifCounterDiscontinuityTime`=VALUES(`ifCounterDiscontinuityTime`),"
					"`rrdpath`=VALUES(`rrdpath`);";
	
	int Count=0;
	std::string sqlbuff;
	MAPIFTABLEINFO::iterator it=m_ifTable.begin();
	while(it!=m_ifTable.end()){
		char tmp[2048]={0};
		char tmpifAlias[256]={0};
		char tmpifName[256]={0};
		ReplaceMySqlStr(it->second.sys.ifAlias,tmpifAlias,255);
		ReplaceMySqlStr(it->second.sys.ifName,tmpifName,255);
		snprintf(tmp,2048,"('%s',%u,%u,'%s','%s','%s','%s',"
			"%hu,'%s',%hu,%u,'%s',%hhu,%hhu,%u,%hhu,%u,'%s')",m_ip.c_str(),it->first,it->second.ifCrc32,
			it->second.ifDescr,it->second.sys.ifFullDescr,tmpifAlias,tmpifName,
			it->second.sys.ifType,it->second.sys.ifTypeDescr,it->second.sys.ifMtu,it->second.sys.ifHighSpeed,
			it->second.sys.ifPhysAddress,it->second.sys.ifAdminStatus,it->second.sys.ifOperStatus,
			it->second.sys.ifLastChange,it->second.sys.ifConnectorPresent,it->second.sys.ifCounterDiscontinuityTime,
			it->second.rrdpath);
		if(Count==0){
			sqlbuff=Head;
		}else{
			sqlbuff+=",";
		}
//更新RRD数据
//判断文件是否存在，不在创建		
		if(is_file_exist(it->second.rrdpath)<1){
			CreateTrafficRRDTool(it->second.rrdpath,it->second.timestamp-300);
		}
//更新数据		
		UpdateTrafficRRDTool(it->second.rrdpath,it->second.timestamp,it->second.octets.ifHCInOctets,it->second.octets.ifHCOutOctets);
//获取最后一条数据
		RRDDateInfo*rrddata=GetRRDData(it->second.rrdpath,it->second.timestamp-300,it->second.timestamp);
		//ShowRRDData(rrddata);
		if(rrddata){
			it->second.octets.iftimestamp=rrddata->start_tm+rrddata->step;
			//要判断NAN这种情况
			if((rrddata->data[0]+rrddata->data[1])>0){
				it->second.octets.ifInFlow=rrddata->data[0];
				it->second.octets.ifOutFlow=rrddata->data[1];
			}
			FreeRRDData(rrddata);
		}
		sqlbuff+=tmp;
		Count++;
		it++;
	}
	if(Count>0){
		sqlbuff+=End;
		MYSQL*mysql=OpenMySQL(MYSQL_BASE);
		RunMySQL(mysql,sqlbuff.c_str());
		CloseMySQL(mysql);
	}	
//////////////////////////////////////////////////	
//存储带宽数据	
	char HeadStr[1024]={0};
	snprintf(HeadStr,sizeof(HeadStr),"INSERT INTO`%s`(`ifCrc32`,`timestamp`,`ifInFlow`,`ifOutFlow`)VALUES",m_flowtable);
	const char*EndStr="ON DUPLICATE KEY UPDATE"
					  "`ifInFlow`=VALUES(`ifInFlow`),"
					  "`ifOutFlow`=VALUES(`ifOutFlow`);";
	Count=0;
	it=m_ifTable.begin();
	while(it!=m_ifTable.end()){
		char tmp[1024]={0};
		sprintf(tmp,"(%u,%ld,%0.6f,%0.6f)",
			it->second.ifCrc32,it->second.octets.iftimestamp,it->second.octets.ifInFlow,it->second.octets.ifOutFlow);
		if(Count==0){
			sqlbuff=HeadStr;
		}else{
			sqlbuff+=",";
		}
		sqlbuff+=tmp;
		Count++;
		it++;
	}
	if(Count>0){
		sqlbuff+=EndStr;
		MYSQL*mysql=OpenMySQL(MYSQL_FLOW);
		RunMySQL(mysql,sqlbuff.c_str(),1146,m_createsql);
		CloseMySQL(mysql);
	}

}

#if 0
void CSwitchifTable::UpLoadifOctets(void*mysqlptr){
	MYSQL*mysql=(MYSQL*)mysqlptr;
	if(m_ifTable.empty())return;
	char tablename[64]={0};
	sprintf(tablename,"ifoctets_%s",m_ip.c_str());
	char Head[1024]={0};
	sprintf(Head,"INSERT INTO`%s`(`ifCrc32`,`timestamp`,`ifHCInOctets`,`ifHCOutOctets`)VALUES",tablename);
	const char*End=	"ON DUPLICATE KEY UPDATE"
					"`ifHCInOctets`=VALUES(`ifHCInOctets`),"
					"`ifHCOutOctets`=VALUES(`ifHCOutOctets`);";
	int Count=0;
	std::string sqlbuff;
	MAPIFTABLEINFO::iterator it=m_ifTable.begin();
	while(it!=m_ifTable.end()){
		char tmp[1024]={0};
		sprintf(tmp,"(%u,%ld," PRU64 "," PRU64 ")",
			it->second.ifCrc32,it->second.timestamp,it->second.octets.ifHCInOctets,it->second.octets.ifHCOutOctets);
		if(Count==0){
			sqlbuff=Head;
		}else{
			sqlbuff+=",";
		}
		sqlbuff+=tmp;
		Count++;
		it++;
	}
	sqlbuff+=End;
	char*createsql=MakeCreateIfOctetsTableSQL(m_ip.c_str()); 
	RunMySQL(mysql,sqlbuff.c_str(),1146,createsql);
	free(createsql);
}
#endif
#if 0
void CSwitchifTable::UpdateifFlow(time_t checktime,void*mysqlptr){
	MYSQL*mysql=(MYSQL*)mysqlptr;
	char octetstablename[64]={0};
	sprintf(octetstablename,"ifoctets_%s",m_ip.c_str());
	char flowtablename[64]={0};
	sprintf(flowtablename,"ifflow_%s",m_ip.c_str());
	char timebuff[32]={0};
	sprintf(timebuff,"%ld",checktime);
	char timestr[32]={0};
	timetostr(checktime,timestr,sizeof(timestr));
	INFO("更新一次带宽数据 IP:%s CheckTime:%s(%ld)",m_ip.c_str(),timestr,checktime);
	std::string updatesql="INSERT INTO`ifflow_0.0.0.0`(`ifCrc32`,`timestamp`,`ifInFlow`,`ifOutFlow`)("
"SELECT `tmp2`.`ifCrc32`,@checktime AS`timestamp`,"
"((IFNULL(`tmp1`.`ifInFlow`,0)*IFNULL(`tmp1`.`duringtime`,0)+"
"IFNULL(`tmp2`.`ifInFlow`,0)*IFNULL(`tmp2`.`duringtime`,0)+"
"IFNULL(`tmp3`.`ifInFlow`,0))/"
"(IFNULL(`tmp1`.`duringtime`,0)+IFNULL(`tmp2`.`duringtime`,0)+IFNULL(`tmp3`.`duringtime`,0)))AS`ifInFlow`,"
"((IFNULL(`tmp1`.`ifOutFlow`,0)*IFNULL(`tmp1`.`duringtime`,0)+"
"IFNULL(`tmp2`.`ifOutFlow`,0)*IFNULL(`tmp2`.`duringtime`,0)+"
"IFNULL(`tmp3`.`ifOutFlow`,0))/"
"(IFNULL(`tmp1`.`duringtime`,0)+IFNULL(`tmp2`.`duringtime`,0)+IFNULL(`tmp3`.`duringtime`,0)))AS`ifOutFlow`"
"FROM(SELECT @checktime:=0000000000 AS`checktime`)`X`"
"LEFT JOIN("
"SELECT "
"((`B`.`ifHCInOctets`-`A`.`ifHCInOctets`)/(`B`.`timestamp`-`A`.`timestamp`)) AS`ifInFlow`,"
"((`B`.`ifHCOutOctets`-`A`.`ifHCOutOctets`)/(`B`.`timestamp`-`A`.`timestamp`)) AS`ifOutFlow`,"
"(@checktime-`A`.`timestamp`)AS`duringtime`,`A`.`ifCrc32`"
"FROM("
"SELECT`B`.`timestamp`,`B`.`ifCrc32`,`B`.`ifHCInOctets`,`B`.`ifHCOutOctets`"
"FROM("
"SELECT MAX(`timestamp`)AS`timestamp`,`ifCrc32`"
"FROM`ifoctets_0.0.0.0`"
"WHERE`timestamp`>=(@checktime-300)" 
"AND`timestamp`<@checktime  "
"GROUP BY`ifCrc32`"
")`A`,`ifoctets_0.0.0.0` `B`"
"WHERE`A`.`ifCrc32`=`B`.`ifCrc32`AND`A`.`timestamp`=`B`.`timestamp`"
")`A`,("
"SELECT`B`.`timestamp`,`B`.`ifCrc32`,`B`.`ifHCInOctets`,`B`.`ifHCOutOctets`"
"FROM("
"SELECT MIN(`timestamp`)AS`timestamp`,`ifCrc32`"
"FROM`ifoctets_0.0.0.0`"
"WHERE`timestamp`>=@checktime "
"AND`timestamp`<(@checktime+300) "
"GROUP BY`ifCrc32`"
")`A`,`ifoctets_0.0.0.0` `B`"
"WHERE`A`.`ifCrc32`=`B`.`ifCrc32`AND`A`.`timestamp`=`B`.`timestamp`"
")`B`"
"WHERE`A`.`ifCrc32`=`B`.`ifCrc32`"
")`tmp2`ON(1=1)"
"LEFT JOIN("
"SELECT "
"((`B`.`ifHCInOctets`-`A`.`ifHCInOctets`)/(`B`.`timestamp`-`A`.`timestamp`))AS`ifInFlow`,"
"((`B`.`ifHCOutOctets`-`A`.`ifHCOutOctets`)/(`B`.`timestamp`-`A`.`timestamp`))AS`ifOutFlow`,"
"(`B`.`timestamp`-(@checktime-300))AS`duringtime`,`A`.`ifCrc32`"
"FROM("
"SELECT`B`.`timestamp`,`B`.`ifCrc32`,`B`.`ifHCInOctets`,`B`.`ifHCOutOctets`"
"FROM("
"SELECT MAX(`timestamp`)AS`timestamp`,`ifCrc32`"
"FROM`ifoctets_0.0.0.0`"
"WHERE`timestamp`>=(@checktime-600) "
"AND`timestamp`<(@checktime-300) "
"GROUP BY`ifCrc32`"
")`A`,`ifoctets_0.0.0.0` `B`"
"WHERE`A`.`ifCrc32`=`B`.`ifCrc32`AND`A`.`timestamp`=`B`.`timestamp`"
")`A`,("
"SELECT`B`.`timestamp`,`B`.`ifCrc32`,`B`.`ifHCInOctets`,`B`.`ifHCOutOctets`"
"FROM("
"SELECT MIN(`timestamp`)AS`timestamp`,`ifCrc32`"
"FROM`ifoctets_0.0.0.0`"
"WHERE`timestamp`>=(@checktime-300) "
"AND`timestamp`<@checktime "
"GROUP BY`ifCrc32`"
")`A`,`ifoctets_0.0.0.0` `B`"
"WHERE`A`.`ifCrc32`=`B`.`ifCrc32`AND`A`.`timestamp`=`B`.`timestamp`"
")`B`"
"WHERE`A`.`ifCrc32`=`B`.`ifCrc32`"
")`tmp1`ON(`tmp2`.`ifCrc32`=`tmp1`.`ifCrc32`)"
"LEFT JOIN("
"SELECT "
"(`B`.`ifHCInOctets`-`A`.`ifHCInOctets`)AS`ifInFlow`,"
"(`B`.`ifHCOutOctets`-`A`.`ifHCOutOctets`)AS`ifOutFlow`,"
"(`B`.`timestamp`-`A`.`timestamp`)AS`duringtime`,`A`.`ifCrc32`"
"FROM("
"SELECT`B`.`timestamp`,`B`.`ifCrc32`,`B`.`ifHCInOctets`,`B`.`ifHCOutOctets`"
"FROM("
"SELECT MIN(`timestamp`)AS`timestamp`,`ifCrc32`"
"FROM`ifoctets_0.0.0.0`"
"WHERE`timestamp`>=(@checktime-300) "
"AND`timestamp`<@checktime "
"GROUP BY`ifCrc32`"
")`A`,`ifoctets_0.0.0.0` `B`"
"WHERE`A`.`ifCrc32`=`B`.`ifCrc32`AND`A`.`timestamp`=`B`.`timestamp`"
")`A`,("
"SELECT`B`.`timestamp`,`B`.`ifCrc32`,`B`.`ifHCInOctets`,`B`.`ifHCOutOctets`"
"FROM("
"SELECT MAX(`timestamp`)AS`timestamp`,`ifCrc32`"
"FROM`ifoctets_0.0.0.0`"
"WHERE`timestamp`>=(@checktime-300)  "
"AND `timestamp`<@checktime "
"GROUP BY`ifCrc32`"
")`A`,`ifoctets_0.0.0.0` `B`"
"WHERE`A`.`ifCrc32`=`B`.`ifCrc32`AND`A`.`timestamp`=`B`.`timestamp`"
")`B`"
"WHERE`A`.`ifCrc32`=`B`.`ifCrc32`"
")`tmp3`ON(`tmp2`.`ifCrc32`=`tmp3`.`ifCrc32`)"
"HAVING `tmp2`.`ifCrc32` IS NOT NULL"
")ON DUPLICATE KEY UPDATE"
"`ifInFlow`=VALUES(`ifInFlow`),"
"`ifOutFlow`=VALUES(`ifOutFlow`);";
	replace_all_distinct(updatesql,"ifoctets_0.0.0.0",octetstablename);
	replace_all_distinct(updatesql,"ifflow_0.0.0.0",flowtablename);
	replace_all_distinct(updatesql,"0000000000",timebuff);	
	char*createsql=MakeCreateIfFlowTableSQL(m_ip.c_str()); 
	RunMySQL(mysql,updatesql.c_str(),1146,createsql);
	free(createsql);
}
#endif
#if 0 
void CSwitchifTable::UpDateifFlowAll(time_t starttime,time_t endtime){
	time_t checktime=starttime/300*300;
	char startdescr[32]={0};
	char enddescr[32]={0};
	timetostr(starttime,startdescr,sizeof(startdescr));
	timetostr(starttime,enddescr,sizeof(enddescr));
	INFO("更新IP:%s带宽 范围%s(%ld)~%s(%ld)",m_ip.c_str(),startdescr,starttime,enddescr,endtime);
	MYSQL*mysql=OpenMySQL(MYSQL_FLOW);
	while(checktime<endtime){
		UpdateifFlow(checktime,mysql);
		checktime+=300;
	}
	CloseMySQL(mysql);
}
#endif
#if USE_PUBLIC_TOOL  
#include"PublicRRDTool.h"
void CSwitchifTable::CreateRRDTool(const char*ifDescr,const char*rrdname,time_t starttime,time_t endtime){
	char octetstablename[64]={0};
	sprintf(octetstablename,"ifoctets_%s",m_ip.c_str());
	char selectsql[1024]={0};
	sprintf(selectsql,"SELECT`timestamp`,`ifHCInOctets`,`ifHCOutOctets`"
	"FROM`%s`WHERE`timestamp`BETWEEN %ld AND %ld "
	"AND`ifDescr`='%s' ORDER BY`timestamp`ASC ;",octetstablename,starttime,endtime,ifDescr);
	MYSQL*mysql=OpenMySQL(MYSQL_FLOW);
	printf("%s\n",selectsql);
	my_ulonglong resnum=0;
    MYSQL_RES*res=RunMySQLGetRes(mysql,selectsql,resnum);
	if(res==NULL||resnum==0){
		char startdescr[32]={0};
		char enddescr[32]={0};
		strcpy(startdescr,timetostr(starttime));
		strcpy(enddescr,timetostr(endtime));
		WRONG("未获取到数据 IP:%s ifDesc:%s 范围%s(%ld)~%s(%ld)",m_ip.c_str(),ifDescr,startdescr,starttime,enddescr,endtime);
	}else{
		CreateTrafficRRDTool(rrdname,starttime-300);
		MYSQL_ROW row=NULL;
		while((row=mysql_fetch_row(res))!=NULL){
			time_t timestamp=atol(row[0]);
			uint64_t ifHCInOctets=strtoull(row[1],NULL,10);
			uint64_t ifHCOutOctets=strtoull(row[2],NULL,10);
			UpdateTrafficRRDTool(rrdname,timestamp,ifHCInOctets,ifHCOutOctets);
		}
	}
	FreeMySQLRes(res);
	CloseMySQL(mysql);
}
#endif

MAPIFTABLEINFO CSwitchifTable::GetAllSwitchPortList(const char*IP){
	MAPIFTABLEINFO result;
	char buffer[1024]={0};
	sprintf(buffer,"SELECT`ifIndex`,`ifDescr`,`ifAlias`,`ifPhysAddress`FROM`iftable`WHERE`IP`='%s';",IP);
	MYSQL*mysql=OpenMySQL(MYSQL_BASE);
	my_ulonglong num=0;
	MYSQL_RES*res=RunMySQLGetRes(mysql,buffer,num);
	if(res){
		MYSQL_ROW row;
		while((row=MySQLFetchRow(res))!=NULL){
			ifTableInfo tmp={0};
			strcpy(tmp.ifDescr,row[1]);
			strcpy(tmp.sys.ifAlias,row[2]);
			strcpy(tmp.sys.ifPhysAddress,row[3]);
			uint32_t ifIndex=atoll(row[0]);
			result.insert(std::pair<uint32_t,ifTableInfo>(ifIndex,tmp));
		}
	}else{
		WRONG("IP列表获取失败");
	}
	FreeMySQLRes(res);
	CloseMySQL(mysql);
	return result;
}

